package com.ruoyi.common.utils.poi;

import com.ruoyi.common.utils.file.FileUploadUtils;
import com.ruoyi.framework.config.RuoYiConfig;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.ddf.EscherClientAnchorRecord;
import org.apache.poi.ddf.EscherRecord;
import org.apache.poi.hssf.record.EscherAggregate;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import javax.imageio.stream.FileImageOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel导入工具类
 *
 * @Author： lcx
 * @Date：2018年5月11日上午10:49:09
 */
public class ImportExcelUtil {
    /*
     * 判断Excel后缀格式
     *
     * @Author： lcx
     *
     * @Date：2018年5月11日上午10:52:11
     * 特殊的读取 不要使用 只在第一行是完整表头的时候使用
     */
    public static List<List<Object>> importExcel(File file) throws IOException {
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            return read2003Excel(file);
        } else if ("xlsx".equals(extension)) {
            return read2007Excel(file);
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * @Author lcx
     * @Date 2018/9/17 17:15
     * 原始版本的导入
     */
    public static List<List<Object>> importExcel_AsTwo(File file) throws IOException {
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            return read2003Excel(file);
        } else if ("xlsx".equals(extension)) {
            return read2007Excel_tem(file);
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /*
     * 读取2003Excel
     *
     * @Author： lcx
     *
     * @Date：2018年5月11日上午10:50:02
     */
    private static List<List<Object>> read2003Excel(File file) throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = hwb.getSheetAt(0);
        Object value = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        short last = 0;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            /*此操作会按照第一行的来 限制后面的列的个数*/
            if (i == 0) {
                last = row.getLastCellNum();
            }
            for (int j = row.getFirstCellNum(); j <= last; j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    linked.add("");
                    continue;
                }
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",Locale.CHINA);// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        // System.out.println(i+"行"+j+" 列 is String type");
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        // System.out.println(i+"行"+j+" 列 is Number type ;
                        // DateFormt:"+cell.getCellStyle().getDataFormatString());
                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            value = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                            value = nf.format(cell.getNumericCellValue());
                        } else {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        // System.out.println(i+"行"+j+" 列 is Boolean type");
                        value = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        // System.out.println(i+"行"+j+" 列 is Blank type");
                        value = "";
                        break;
                    default:
                        // System.out.println(i+"行"+j+" 列 is default type");
                        value = cell.toString();
                }
                if (value == null || "".equals(value)) {
                    value = "";
                }
                linked.add(value);
            }
            list.add(linked);
        }
        return list;
    }

    /*
     * 读取2007Excel
     *
     * @Author： lcx
     *
     * @Date：2018年5月11日上午10:50:39
     */
    private static List<List<Object>> read2007Excel(File file) throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);
        Object value = null;
        XSSFRow row = null;
        XSSFCell cell = null;
        short last = 0;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            /*此操作会按照第一行的来 限制后面的列的个数*/
            if (i == 0) {
                last = row.getLastCellNum();
            }
            for (int j = row.getFirstCellNum(); j <= last; j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    linked.add("");
                    continue;
                }
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",Locale.CHINA);// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        // System.out.println(i+"行"+j+" 列 is String type");
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        // System.out.println(i+"行"+j+" 列 is Number type ;
                        // DateFormt:"+cell.getCellStyle().getDataFormatString());
                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            value = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                            value = nf.format(cell.getNumericCellValue());
                        } else {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        // System.out.println(i+"行"+j+" 列 is Boolean type");
                        value = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        // System.out.println(i+"行"+j+" 列 is Blank type");
                        value = "";
                        break;
                    default:
                        // System.out.println(i+"行"+j+" 列 is default type");
                        value = cell.toString();
                }
                if (value == null || "".equals(value)) {
                    value = "";
                }
                linked.add(value);
            }
            list.add(linked);
        }
        return list;
    }


    /*
     * 读取2007Excel
     *
     * @Author： lcx
     *
     * @Date：2018年5月11日上午10:50:39
     */
    private static List<List<Object>> read2007Excel_tem(File file) throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);
        Object value = null;
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    linked.add("");
                    continue;
                }
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",Locale.CHINA);// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        // System.out.println(i+"行"+j+" 列 is String type");
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        // System.out.println(i+"行"+j+" 列 is Number type ;
                        // DateFormt:"+cell.getCellStyle().getDataFormatString());
                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            value = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                            value = nf.format(cell.getNumericCellValue());
                        } else {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        // System.out.println(i+"行"+j+" 列 is Boolean type");
                        value = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        // System.out.println(i+"行"+j+" 列 is Blank type");
                        value = "";
                        break;
                    default:
                        // System.out.println(i+"行"+j+" 列 is default type");
                        value = cell.toString();
                }
                if (value == null || "".equals(value)) {
                    value = "";
                }
                linked.add(value);
            }
            list.add(linked);
        }
        return list;
    }

    /*
     * 测试
     *
     * @Author： lcx
     *
     * @Date：2018年5月11日上午10:54:01
     */
    public static List<Object> getImage(File file) {

        List<Object> fileNameList = new ArrayList<>();

        HSSFWorkbook xwb = null;
        try {
            xwb = new HSSFWorkbook(new FileInputStream(file));
        } catch (IOException e) {
            e.printStackTrace();
        }
        //读取图片
        List<HSSFPictureData> pictures = xwb.getAllPictures();
        for (int z = 0; z < pictures.size(); z++) {
            HSSFPictureData pictureData = pictures.get(z);
            byte[] picData = pictureData.getData();
            System.out.println("image-size:" + picData.length);
            String fileName = FileUploadUtils.encodingFilenameAsUUID("batchProductImg.jpg", ".jpg");

            byte2image(picData, fileName);
            fileNameList.add(fileName);
        }

        return fileNameList;
    }

    /**
     * @Author lcx
     * @Date 2018/10/10 17:05
     * 获得高级排序
     */
    public static List<Integer> getImageAsRowNum(File file) {

        List<Integer> refileNameList = new ArrayList<>();

        HSSFWorkbook xwb = null;
        try {
            xwb = new HSSFWorkbook(new FileInputStream(file));
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<ClientAnchorInfo> clientAnchorRecords = getClientAnchorRecords(xwb);

        //读取图片
        List<HSSFPictureData> pictures = xwb.getAllPictures();
        for (int z = 0; z < pictures.size(); z++) {
            HSSFPictureData pictureData = pictures.get(z);

            //计算图片位置
            ClientAnchorInfo anchor = clientAnchorRecords.get(z);
            HSSFSheet sheet = anchor.sheet;
            EscherClientAnchorRecord clientAnchorRecord = anchor.clientAnchorRecord;
            MyPictureData myPictureData = new MyPictureData(xwb, sheet, pictureData, clientAnchorRecord);
            refileNameList.add((int) myPictureData.getRow0());
        }

        return refileNameList;
    }


    public static void byte2image(byte[] data, String fileName) {
        String path = RuoYiConfig.getProfile() + "product/";
        if (data.length < 3 || path.equals("")) return;
        try {
            FileImageOutputStream imageOutput = new FileImageOutputStream(new File(path, fileName));
            imageOutput.write(data, 0, data.length);
            imageOutput.close();
            System.out.println("Make Picture success,Please find image in " + path);
        } catch (Exception ex) {
            System.out.println("Exception: " + ex);
            ex.printStackTrace();
        }
    }

    //测试

    public static List<MyPictureData> getAllPictures(HSSFWorkbook workbook) {
        List<MyPictureData> list = new ArrayList<MyPictureData>();

        List<HSSFPictureData> pictureList = workbook.getAllPictures();
        List<ClientAnchorInfo> clientAnchorRecords = getClientAnchorRecords(workbook);

        if (pictureList.size() != clientAnchorRecords.size()) {
            throw new RuntimeException("解析文件中的图片信息出错，找到的图片数量和图片位置信息数量不匹配");
        }

        for (int i = 0; i < pictureList.size(); i++) {
            HSSFPictureData pictureData = pictureList.get(i);
            ClientAnchorInfo anchor = clientAnchorRecords.get(i);
            HSSFSheet sheet = anchor.sheet;
            EscherClientAnchorRecord clientAnchorRecord = anchor.clientAnchorRecord;
            list.add(new MyPictureData(workbook, sheet, pictureData, clientAnchorRecord));
        }

        return list;
    }

    private static class ClientAnchorInfo {
        public HSSFSheet sheet;
        public EscherClientAnchorRecord clientAnchorRecord;

        public ClientAnchorInfo(HSSFSheet sheet, EscherClientAnchorRecord clientAnchorRecord) {
            super();
            this.sheet = sheet;
            this.clientAnchorRecord = clientAnchorRecord;
        }
    }

    private static List<ClientAnchorInfo> getClientAnchorRecords(HSSFWorkbook workbook) {
        List<ClientAnchorInfo> list = new ArrayList<ClientAnchorInfo>();

        EscherAggregate drawingAggregate = null;
        HSSFSheet sheet = null;
        List<EscherRecord> recordList = null;
        Iterator<EscherRecord> recordIter = null;
        int numSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numSheets; i++) {
            sheet = workbook.getSheetAt(i);
            drawingAggregate = sheet.getDrawingEscherAggregate();
            if (drawingAggregate != null) {
                recordList = drawingAggregate.getEscherRecords();
                recordIter = recordList.iterator();
                while (recordIter.hasNext()) {
                    getClientAnchorRecords(sheet, recordIter.next(), 1, list);
                }
            }
        }

        return list;
    }

    private static void getClientAnchorRecords(HSSFSheet sheet, EscherRecord escherRecord, int level, List<ClientAnchorInfo> list) {
        List<EscherRecord> recordList = null;
        Iterator<EscherRecord> recordIter = null;
        EscherRecord childRecord = null;
        recordList = escherRecord.getChildRecords();
        recordIter = recordList.iterator();
        while (recordIter.hasNext()) {
            childRecord = recordIter.next();
            if (childRecord instanceof EscherClientAnchorRecord) {
                ClientAnchorInfo e = new ClientAnchorInfo(sheet, (EscherClientAnchorRecord) childRecord);
                list.add(e);
            }
            if (childRecord.getChildRecords().size() > 0) {
                getClientAnchorRecords(sheet, childRecord, level + 1, list);
            }
        }
    }

}
